package com.lomboardrisk.app.ee_new.web.actions;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;


public class JGridActionByJDBC extends DispatchAction {

	protected ActionForward unspecified(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
	throws Exception {
		
		return null;
	}
	
	public ActionForward list(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		
		long page = Long.parseLong(request.getParameter("page"));
		long pageSize = Long.parseLong(request.getParameter("rows"));
		
		//test database
		Context ctx=new InitialContext();
		DataSource ds=(DataSource)ctx.lookup("java:comp/env/jdbc/orcl");
		
		Connection conn = ds.getConnection();
		String sql = "select e.id, e.name, e.sex, e.hobby, e.birthdate from ( " +
					  "select rownum rnum, t.* from ( " +
					     "select g.* from jqGrid g order by id " + 
					  ") t " + 
					") e where e.rnum > ? and e.rnum <= ? ";
		
		String totalSql = "select count(*) from jqGrid";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
//		pstmt.setString(1, "id");
		pstmt.setLong(1, (page-1)*pageSize);
		pstmt.setLong(2, page*pageSize);
		
		ResultSet rs = pstmt.executeQuery();
		
//		List<JqGridItem> items = new ArrayList<JqGridItem>();
//		JqGridItem item = null;
//		while (rs.next()) {
//			item = new JqGridItem();
//			item.setId(rs.getInt(1));
//			item.setName(rs.getString(2));
//			item.setSex(rs.getBoolean(3));
//			item.setHobby(rs.getInt(4));
//			item.setBirthDate(rs.getTimestamp(5));
////			item.setBirthDate(rs.getDate(5));
//			items.add(item);
//		}
//		pstmt = conn.prepareStatement(totalSql);
//		rs = pstmt.executeQuery();
//		long totalrecords = 0;
//		while(rs.next()) {
//			totalrecords = rs.getLong(1);
//		}
		
		rs.close();
		pstmt.close();
		conn.close();
		
		
		return null;
	}

}
